# vim: set fileencoding=utf-8 :
import psycopg2 as pg

from config import conf

class DBException(BaseException):
    
    def __init__(self,s):
        self.method = s
        
    def __str__(self):
        return "Error in Method -> %s" % self.method

CONNECTION=None

def connection():
    global CONNACTION
    try:
        CONNACTION = pg.connect(host=conf.HOST,user=conf.USER,password=conf.PASSWORD,database=conf.DB)
        return CONNACTION
    except DBException:
        raise DBException("connection")

def cursor(conn=None):
    if conn==None:
        conn = connection()
    return conn.cursor()

def getCustomers(limit=20):
    sql = "select * from customer_view limit %d;" % limit
    curs = cursor()
    curs.execute(sql)
    return curs.fetchall()

def getCustomersAsc(limit=20):
    sql = "select * from customer_view limit %d order by asc;" % limit
    curs = cursor()
    curs.execute(sql)
    return curs.fetchall()

def getCustomersDesc():
    # Nur als Test
    limit = 20
    sql = "select * from customer_view limit %d order by desc;" % limit
    curs = cursor()
    curs.execute(sql)
    return curs.fetchall()

def getOrders():
    # Nur als Test
    limit = 20
    sql = "select * from orders limit %d;" % limit
    curs = cursor()
    curs.execute(sql)
    return curs.fetchall()

def getProducts():
    # Nur als Test
    limit = 20
    sql = "select * from products limit %d;" % limit
    curs = cursor()
    curs.execute(sql)
    return curs.fetchall()

def getLogin(name,pwd):
    sql = "select username from customers where username=%s and password=%s" % (name,pwd)
    curs = cursor()
    curs.execute(sql)
    return curs.fetchone()

def getCategories():
    sql = "select categoryname from categories"
    curs = cursor()
    curs.execute(sql)
    return curs.fetchall()

def getProductsName():
    # TODO: Alle Produkte zur ausgewählen Categrie auslesen
    sql = "select title from products"
    curs = cursor()
    curs.execute(sql)
    return curs.fetchall()

def getInventory():
    sql = "select quan_in_stock,sales from inventory"
    curs = cursor()
    curs.execute(sql)
    return curs.fetchall()

def insertOrder():
    # TODO: Bestellung in DB schreiben
    sql = "insert into orders ..."
    curs = cursor()
    try:
        curs.execute(sql)
        CONNACTION.commit()
    except:
        CONNACTION.rollback()
